package l1j.opqlo.NewSystem;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import l1j.opqlo.NewSystem.ItemLevelUp.ItemLevelUpTable;
import l1j.opqlo.NewSystem.ItemLevelUp.LevelStatus;
import l1j.server.DatabaseFactory;
import l1j.server.server.datatables.ItemTable;
import l1j.server.server.model.Instance.L1ItemInstance;
import l1j.server.server.templates.L1Item;
import l1j.server.server.utils.SQLUtil;

public class trytry {

	private static ArrayList<L1ItemInstance> items = null;;

	public void load() throws Exception {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			items = new ArrayList<L1ItemInstance>();
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement("SELECT * FROM character_items");

			L1ItemInstance item;
			rs = pstm.executeQuery();
			while (rs.next()) {
				final int itemId = rs.getInt("item_id");
				final L1Item itemTemplate = ItemTable.getInstance().getTemplate(
						itemId);
				if (itemTemplate == null) {
					// _log.warning(String.format("item id:%d not found",
					// itemId));
					continue;
				}
				item = new L1ItemInstance();
				item.setId(rs.getInt("id"));
				item.setItem(itemTemplate);
				// TODO 飾品強化卷軸
				// TODO 魔法武器卷軸by missu0524
				final int skilltype = rs.getInt("skilltype");
				item.setSkillType(0);
				// TODO 魔法武器卷軸by missu0524
				if (skilltype == 0) {
					continue;
				}
				// opqlo 道具升級系統START
				final int level = rs.getInt("level");
				if (level > 1) {
					item.setLevel(1);
					ItemLevelUpTable.getInstance().getLevelStatus(item);
				}
				if (ItemLevelUpTable.getInstance()
						.getTemplate(item.getItemId()) == null) { // opqlo
																	// 不在升級清單中
					final LevelStatus lvs = item.getLvStatus();
					switch (skilltype) {
					case 1:
						lvs.setEthnicityDmg(1, level);
						break;
					case 2:
						lvs.setEthnicityDmg(2, level);
						break;
					case 3:
						lvs.setEthnicityDmg(3, level);
						break;
					case 4:
						lvs.setEthnicityDmg(5, level);
						break;
					case 5:
						lvs.setEthnicityDmg(6, level);
						break;
					case 6:
						lvs.setEthnicityDmg(0, level);
						break;
					case 7:
						item.setBigDmg(level);
						break;
					case 8:
						item.setSmallDmg(level);
						break;
					case 127:
						item.setExpUp(level / 100D);
						break;
					}
					items.add(item);
				}
				// opqlo 道具升級系統END
				/*
				 * item.getLvStatus().setItemSpecialDmg(item ,
				 * rs.getString("屬性增傷") ,0);
				 * item.getLvStatus().setItemSpecialDmg(item ,
				 * rs.getString("六屬性加成") ,1); item.setExtraDmg(rs.getInt("破甲"));
				 * item.setExtraDef(rs.getInt("抵擋"));
				 * item.setModDmg(rs.getInt("額外攻擊"));
				 * item.setModHit(rs.getInt("攻擊成功"));
				 * item.setExpUp(rs.getDouble("經驗"));
				 * item.setDoubleDmg(rs.getInt("爆擊"));
				 * item.setBigDmg(rs.getInt("大怪"));
				 * item.setSmallDmg(rs.getInt("小怪"));
				 * item.setaddMr(rs.getInt("魔防"));
				 * item.getLastStatus().updateAll();
				 */

			}
		} catch (final SQLException e) {
			throw e;
		} finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		this.store();
	}

	private void loadclanwarehouse() throws Exception {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			items = new ArrayList<L1ItemInstance>();
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement("SELECT * FROM clan_warehouse");

			L1ItemInstance item;
			rs = pstm.executeQuery();
			while (rs.next()) {
				final int itemId = rs.getInt("item_id");
				final L1Item itemTemplate = ItemTable.getInstance().getTemplate(
						itemId);
				if (itemTemplate == null) {
					// _log.warning(String.format("item id:%d not found",
					// itemId));
					continue;
				}
				item = new L1ItemInstance();
				item.setId(rs.getInt("id"));
				item.setItem(itemTemplate);
				// TODO 飾品強化卷軸
				// TODO 魔法武器卷軸by missu0524
				final int skilltype = rs.getInt("skilltype");
				item.setSkillType(0);
				// TODO 魔法武器卷軸by missu0524
				if (skilltype == 0) {
					continue;
				}
				// opqlo 道具升級系統START
				final int level = rs.getInt("level");
				if (level > 1) {
					item.setLevel(1);
					ItemLevelUpTable.getInstance().getLevelStatus(item);
				}
				if (ItemLevelUpTable.getInstance()
						.getTemplate(item.getItemId()) == null) { // opqlo
																	// 不在升級清單中
					final LevelStatus lvs = item.getLvStatus();
					switch (skilltype) {
					case 1:
						lvs.setEthnicityDmg(1, level);
						break;
					case 2:
						lvs.setEthnicityDmg(2, level);
						break;
					case 3:
						lvs.setEthnicityDmg(3, level);
						break;
					case 4:
						lvs.setEthnicityDmg(5, level);
						break;
					case 5:
						lvs.setEthnicityDmg(6, level);
						break;
					case 6:
						lvs.setEthnicityDmg(0, level);
						break;
					case 7:
						item.setBigDmg(level);
						break;
					case 8:
						item.setSmallDmg(level);
						break;
					case 127:
						item.setExpUp(level / 100D);
						break;
					}
					items.add(item);
				}
				// opqlo 道具升級系統END
				/*
				 * item.getLvStatus().setItemSpecialDmg(item ,
				 * rs.getString("屬性增傷") ,0);
				 * item.getLvStatus().setItemSpecialDmg(item ,
				 * rs.getString("六屬性加成") ,1); item.setExtraDmg(rs.getInt("破甲"));
				 * item.setExtraDef(rs.getInt("抵擋"));
				 * item.setModDmg(rs.getInt("額外攻擊"));
				 * item.setModHit(rs.getInt("攻擊成功"));
				 * item.setExpUp(rs.getDouble("經驗"));
				 * item.setDoubleDmg(rs.getInt("爆擊"));
				 * item.setBigDmg(rs.getInt("大怪"));
				 * item.setSmallDmg(rs.getInt("小怪"));
				 * item.setaddMr(rs.getInt("魔防"));
				 * item.getLastStatus().updateAll();
				 */

			}
		} catch (final SQLException e) {
			throw e;
		} finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		this.storeclanwarehouse();
	}

	private void loadwarehouse() throws Exception {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			items = new ArrayList<L1ItemInstance>();
			con = DatabaseFactory.get().getConnection();
			pstm = con.prepareStatement("SELECT * FROM character_warehouse");

			L1ItemInstance item;
			rs = pstm.executeQuery();
			while (rs.next()) {
				final int itemId = rs.getInt("item_id");
				final L1Item itemTemplate = ItemTable.getInstance().getTemplate(
						itemId);
				if (itemTemplate == null) {
					// _log.warning(String.format("item id:%d not found",
					// itemId));
					continue;
				}
				item = new L1ItemInstance();
				item.setId(rs.getInt("id"));
				item.setItem(itemTemplate);
				// TODO 飾品強化卷軸
				// TODO 魔法武器卷軸by missu0524
				final int skilltype = rs.getInt("skilltype");
				item.setSkillType(0);
				// TODO 魔法武器卷軸by missu0524
				if (skilltype == 0) {
					continue;
				}
				// opqlo 道具升級系統START
				final int level = rs.getInt("level");
				if (level > 1) {
					item.setLevel(1);
					ItemLevelUpTable.getInstance().getLevelStatus(item);
				}
				if (ItemLevelUpTable.getInstance()
						.getTemplate(item.getItemId()) == null) { // opqlo
																	// 不在升級清單中
					final LevelStatus lvs = item.getLvStatus();
					switch (skilltype) {
					case 1:
						lvs.setEthnicityDmg(1, level);
						break;
					case 2:
						lvs.setEthnicityDmg(2, level);
						break;
					case 3:
						lvs.setEthnicityDmg(3, level);
						break;
					case 4:
						lvs.setEthnicityDmg(5, level);
						break;
					case 5:
						lvs.setEthnicityDmg(6, level);
						break;
					case 6:
						lvs.setEthnicityDmg(0, level);
						break;
					case 7:
						item.setBigDmg(level);
						break;
					case 8:
						item.setSmallDmg(level);
						break;
					case 127:
						item.setExpUp(level / 100D);
						break;
					}
					items.add(item);
				}
				// opqlo 道具升級系統END
				/*
				 * item.getLvStatus().setItemSpecialDmg(item ,
				 * rs.getString("屬性增傷") ,0);
				 * item.getLvStatus().setItemSpecialDmg(item ,
				 * rs.getString("六屬性加成") ,1); item.setExtraDmg(rs.getInt("破甲"));
				 * item.setExtraDef(rs.getInt("抵擋"));
				 * item.setModDmg(rs.getInt("額外攻擊"));
				 * item.setModHit(rs.getInt("攻擊成功"));
				 * item.setExpUp(rs.getDouble("經驗"));
				 * item.setDoubleDmg(rs.getInt("爆擊"));
				 * item.setBigDmg(rs.getInt("大怪"));
				 * item.setSmallDmg(rs.getInt("小怪"));
				 * item.setaddMr(rs.getInt("魔防"));
				 * item.getLastStatus().updateAll();
				 */

			}
		} catch (final SQLException e) {
			throw e;
		} finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		this.storewarehouse();
	}

	private void store() throws Exception {
		for (int i = 0; i < items.size(); i++) {
			final L1ItemInstance item = items.get(i);

			Connection con = null;
			PreparedStatement pstm = null;

			try {
				con = DatabaseFactory.get().getConnection();
				pstm = con
						.prepareStatement("UPDATE character_items SET SkillType= ?, level=?,  種族增傷=? , 經驗=? , 大怪=?, 小怪=?  WHERE id = ?");
				// pstm =
				// con.prepareStatement("UPDATE opqlo_laba SET 場次=?, 轉數=?, 轉中次數=?, 槓龜次數=?, 累計彩金=?, 上一次得主=? WHERE 場次=?");
				pstm.setInt(7, item.getId());
				// TODO 魔法武器卷軸by missu0524
				pstm.setInt(1, 0);
				// TODO 魔法武器卷軸by missu0524
				// opqlo 道具升級系統START
				pstm.setInt(2, 1);
				// opqlo 道具升級系統END
				pstm.setString(3, item.getLvStatus().saveItemSpecialDmg(2));
				pstm.setDouble(4, item.getExpUp());
				pstm.setDouble(5, item.getBigDmg());
				pstm.setDouble(6, item.getSmallDmg());
				pstm.execute();
				System.out.println(item.getName());
				Thread.sleep(5);
			} catch (final SQLException e) {
				throw e;
			} finally {
				SQLUtil.close(pstm);
				SQLUtil.close(con);
			}

		}
		items.clear();
		items = null;
		this.loadwarehouse();
	}

	private void storeclanwarehouse() throws Exception {
		for (int i = 0; i < items.size(); i++) {
			final L1ItemInstance item = items.get(i);

			Connection con = null;
			PreparedStatement pstm = null;

			try {
				con = DatabaseFactory.get().getConnection();
				pstm = con
						.prepareStatement("UPDATE clan_warehouse SET SkillType= ?, level=?,  種族增傷=? , 經驗=? , 大怪=?, 小怪=?  WHERE id = ?");
				// pstm =
				// con.prepareStatement("UPDATE opqlo_laba SET 場次=?, 轉數=?, 轉中次數=?, 槓龜次數=?, 累計彩金=?, 上一次得主=? WHERE 場次=?");
				pstm.setInt(7, item.getId());
				// TODO 魔法武器卷軸by missu0524
				pstm.setInt(1, 0);
				// TODO 魔法武器卷軸by missu0524
				// opqlo 道具升級系統START
				pstm.setInt(2, 1);
				// opqlo 道具升級系統END
				pstm.setString(3, item.getLvStatus().saveItemSpecialDmg(2));
				pstm.setDouble(4, item.getExpUp());
				pstm.setDouble(5, item.getBigDmg());
				pstm.setDouble(6, item.getSmallDmg());
				pstm.execute();
				System.out.println(item.getName());
				Thread.sleep(5);
			} catch (final SQLException e) {
				throw e;
			} finally {
				SQLUtil.close(pstm);
				SQLUtil.close(con);
			}

		}
		items.clear();
		items = null;
	}

	private void storewarehouse() throws Exception {
		for (int i = 0; i < items.size(); i++) {
			final L1ItemInstance item = items.get(i);

			Connection con = null;
			PreparedStatement pstm = null;

			try {
				con = DatabaseFactory.get().getConnection();
				pstm = con
						.prepareStatement("UPDATE character_warehouse SET SkillType= ?, level=?,  種族增傷=? , 經驗=? , 大怪=?, 小怪=?  WHERE id = ?");
				// pstm =
				// con.prepareStatement("UPDATE opqlo_laba SET 場次=?, 轉數=?, 轉中次數=?, 槓龜次數=?, 累計彩金=?, 上一次得主=? WHERE 場次=?");
				pstm.setInt(7, item.getId());
				// TODO 魔法武器卷軸by missu0524
				pstm.setInt(1, 0);
				// TODO 魔法武器卷軸by missu0524
				// opqlo 道具升級系統START
				pstm.setInt(2, 1);
				// opqlo 道具升級系統END
				pstm.setString(3, item.getLvStatus().saveItemSpecialDmg(2));
				pstm.setDouble(4, item.getExpUp());
				pstm.setDouble(5, item.getBigDmg());
				pstm.setDouble(6, item.getSmallDmg());
				pstm.execute();
				System.out.println(item.getName());
				Thread.sleep(5);
			} catch (final SQLException e) {
				throw e;
			} finally {
				SQLUtil.close(pstm);
				SQLUtil.close(con);
			}

		}
		items.clear();
		items = null;
		this.loadclanwarehouse();
	}
}
